﻿Imports System.Data.SqlClient
Imports System.Windows.Forms
Imports System.Configuration

Public Class DataProvider
    Public connstr As String = "Data Source=PYYPYLYN-PC\SQLEXPRESS;Initial Catalog=HTDNLMT;Integrated Security=True"
    Public connectionary As New SqlConnection(connstr)
    Public cnn As String = ""
    Public Shared Function getConnection() As SqlConnection

        Dim theSqlServer As SqlConnection = New SqlConnection("Data Source=CHANHLT-PC\SQLEXPRESS; Initial Catalog=HTDNLMT; Integrated Security=SSPI;")
        'Dim theSqlCommand As SqlCommand = New SqlCommand("SELECT * FROM [dbo].[Emplpoyees]", theSqlServer)

        Return theSqlServer

    End Function
    Public Shared Function GetConnectionString() As String
        Dim cnns As String = ConfigurationManager.ConnectionStrings("Cnstr").ConnectionString
        If cnns.StartsWith("#") = True Then
            Return "#"
        End If
        Dim host As String = cnns.Remove(0, 12)
        host = host.Split(";")(0)
        Return host
    End Function
    Public Shared Function TestConnection() As Boolean
        Dim cnns As String = ConfigurationManager.ConnectionStrings("Cnstr").ConnectionString
        Dim cn As New SqlConnection(cnns)
        cn.Open()
        Dim cmd = "use HTDNLMT"
        Try
            Dim da As New SqlCommand(cmd, cn)
            da.ExecuteNonQuery()
        Catch ex As Exception
            cn.Close()
            Return False
        End Try
        cn.Close()
        Return True
    End Function
    Public Shared Function TestConnection_Hoster(hostname As String) As Boolean
        Dim cnns As String = "Data Source=" + hostname + ";Initial Catalog=HTDNLMT;Integrated Security=True"
        Dim cn As New SqlConnection(cnns)
        Try
            cn.Open()
            Dim cmd = "use HTDNLMT"

            Dim da As New SqlCommand(cmd, cn)
            da.ExecuteNonQuery()
        Catch ex As Exception
            cn.Close()
            Return False
        End Try
        cn.Close()
        Return True
    End Function
    Public Shared Function GetConnectionString_Host() As String
        Dim cnns As String = ConfigurationManager.ConnectionStrings("Cnstr").ConnectionString
       
            Return cnns

    End Function
    Public Shared Sub StoreConnectionString(hostname As String)
        Dim config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)
        Dim connectionStringsSection = DirectCast(config.GetSection("connectionStrings"), ConnectionStringsSection)
        connectionStringsSection.ConnectionStrings("Cnstr").ConnectionString = "Data Source=" + hostname + ";Initial Catalog=HTDNLMT;Integrated Security=True"
        config.Save()
        ConfigurationManager.RefreshSection("connectionStrings")
    End Sub
    Public Sub KetNoiCSDL()
        Try
            connectionary.Open()
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Thông báo lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub
    Public Sub DongKetNoi()
        connectionary.Close()
    End Sub
    Private con As SqlConnection
    Public Shared Function ConnectDB() As SqlConnection
        Dim connectionString As String
        connectionString = GetConnectionString_Host()
        
        Dim conn As New SqlConnection(connectionString)
        conn.Open()
        Return conn
    End Function

    Public Shared Function Connection() As SqlConnection
        Dim cnStr As String = GetConnectionString_Host()
        Dim cn As New SqlConnection(cnStr)
        cn.Open()
        Return cn
    End Function

    Public Shared Function ExecQuery(ByVal cmd As SqlCommand) As DataTable
        Dim dt As New DataTable()
        Dim sda As New SqlDataAdapter(cmd)
        sda.Fill(dt)
        Return dt
    End Function

    Public Shared Sub ExecNonQuery(ByVal cmd As SqlCommand)
        Dim sda As New SqlDataAdapter(cmd)
    End Sub
End Class

Public Class ConnectDB
    'Private connString As String = "Data Source=.\SQLEXPRESS;Initial Catalog=JessupDB;Integrated Security=True"
    Shared connString As String = DataProvider.GetConnectionString_Host()

    Private conn As SqlConnection
    Private cmd As SqlCommand

    Public Function Connect() As Boolean
        conn = New SqlConnection(connString)
        Try
            conn.Open()
        Catch ex As Exception
            System.Windows.Forms.MessageBox.Show("Connnection failed. View Details " + ex.Message)

        End Try
        Return True
    End Function
    Public Sub Close()
        If conn.State = ConnectionState.Open Then
            conn.Close()
        End If
        conn.Dispose()
    End Sub

    Public Function Execute(ByVal query As String) ' INSERT/UPDTE/DELETE
        cmd = New SqlCommand()
        Connect()
        cmd.Connection = conn
        cmd.CommandText = query
        Return cmd.ExecuteNonQuery()
    End Function
    Public Function GetData(ByVal strsql As String) As DataTable
        cmd = New SqlCommand()
        Connect()
        cmd.Connection = conn
        cmd.CommandText = strsql

        Dim dr As SqlDataReader = cmd.ExecuteReader()
        Dim dt As New DataTable
        dt.Load(dr)
        Close()
        Return dt
    End Function

    Public Sub LoadComboListbox(ByVal cb As Object, ByVal sql As String, ByVal fieldname As String)
        Dim dt As DataTable
        Dim ketnoi = New ConnectDB()
        dt = ketnoi.GetData(sql)

        For i As Integer = 0 To dt.Rows.Count() - 1
            cb.Items.Add(dt.Rows(i)(fieldname).ToString)
        Next

    End Sub

End Class